package cn.iocoder.yudao.module.system.dal.mysql.userretention;

import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
import cn.iocoder.yudao.framework.mybatis.core.query.LambdaQueryWrapperX;
import cn.iocoder.yudao.module.system.controller.admin.userretention.vo.UserRetentionPageReqVO;
import cn.iocoder.yudao.module.system.dal.dataobject.userretention.UserRetentionStatsDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;

/**
 * 用户留存率统计 Mapper
 *
 * @author 系统开发团队
 */
@Mapper
public interface UserRetentionStatsMapper extends BaseMapperX<UserRetentionStatsDO> {

    /**
     * 分页查询用户留存率统计
     */
    default PageResult<UserRetentionStatsDO> selectPage(UserRetentionPageReqVO pageReqVO) {
        return selectPage(pageReqVO, new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .eqIfPresent(UserRetentionStatsDO::getUserId, pageReqVO.getUserId())
                .likeIfPresent(UserRetentionStatsDO::getUsername, pageReqVO.getUsername())
                .eqIfPresent(UserRetentionStatsDO::getUserType, pageReqVO.getUserType())
                .eqIfPresent(UserRetentionStatsDO::getCodeId, pageReqVO.getCodeId())
                .likeIfPresent(UserRetentionStatsDO::getCode, pageReqVO.getCode())
                .betweenIfPresent(UserRetentionStatsDO::getCodeUsageDate, pageReqVO.getCodeUsageDateStart(), pageReqVO.getCodeUsageDateEnd())
                .eqIfPresent(UserRetentionStatsDO::getRetentionStatus, pageReqVO.getRetentionStatus())
                .eqIfPresent(UserRetentionStatsDO::getRetention3Days, pageReqVO.getRetention3Days())
                .eqIfPresent(UserRetentionStatsDO::getRetention7Days, pageReqVO.getRetention7Days())
                .eqIfPresent(UserRetentionStatsDO::getRetention15Days, pageReqVO.getRetention15Days())
                .betweenIfPresent(UserRetentionStatsDO::getCalculatedTime, pageReqVO.getCalculatedTimeStart(), pageReqVO.getCalculatedTimeEnd())
                .orderByDesc(UserRetentionStatsDO::getCodeUsageDate));
    }

    /**
     * 查询待计算留存率的记录
     */
    default List<UserRetentionStatsDO> selectPendingCalculationByDate(LocalDate targetDate) {
        return selectList(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .eq(UserRetentionStatsDO::getCodeUsageDate, targetDate)
                .eq(UserRetentionStatsDO::getRetentionStatus, 0) // 0-计算中
                .orderByAsc(UserRetentionStatsDO::getId));
    }

    /**
     * 根据用户ID和使用日期查询留存率统计
     */
    default List<UserRetentionStatsDO> selectByUserIdAndDate(Long userId, LocalDate usageDate) {
        return selectList(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .eq(UserRetentionStatsDO::getUserId, userId)
                .eq(UserRetentionStatsDO::getCodeUsageDate, usageDate));
    }

    /**
     * 根据用户ID和使用日期查询留存率统计（返回单个对象）
     */
    default UserRetentionStatsDO selectOneByUserIdAndDate(Long userId, LocalDate usageDate) {
        return selectOne(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .eq(UserRetentionStatsDO::getUserId, userId)
                .eq(UserRetentionStatsDO::getCodeUsageDate, usageDate)
                .orderByDesc(UserRetentionStatsDO::getId)
                .last("LIMIT 1"));
    }

    /**
     * 查询用户在日期范围内的留存率记录
     */
    default List<UserRetentionStatsDO> selectByUserIdAndDateRange(Long userId, LocalDate startDate, LocalDate endDate) {
        return selectList(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .eq(UserRetentionStatsDO::getUserId, userId)
                .betweenIfPresent(UserRetentionStatsDO::getCodeUsageDate, startDate, endDate)
                .orderByDesc(UserRetentionStatsDO::getCodeUsageDate));
    }

    /**
     * 查询日期范围内的留存率记录
     */
    default List<UserRetentionStatsDO> selectByDateRange(LocalDate startDate, LocalDate endDate) {
        return selectList(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .betweenIfPresent(UserRetentionStatsDO::getCodeUsageDate, startDate, endDate)
                .eq(UserRetentionStatsDO::getRetentionStatus, 1) // 1-已计算
                .orderByDesc(UserRetentionStatsDO::getCodeUsageDate));
    }

    /**
     * 根据ID查询留存率统计
     * 
     * @param id 记录ID
     * @return 留存率统计
     */
    UserRetentionStatsDO selectById(@Param("id") Long id);

    /**
     * 根据用户ID和使用记录ID查询
     */
    default UserRetentionStatsDO selectByUserIdAndUsageLogId(Long userId, Long usageLogId) {
        return selectOne(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .eq(UserRetentionStatsDO::getUserId, userId)
                .eq(UserRetentionStatsDO::getUsageLogId, usageLogId));
    }

    /**
     * 检查记录是否存在
     */
    default boolean existsByUserIdAndUsageLogId(Long userId, Long usageLogId) {
        return selectCount(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .eq(UserRetentionStatsDO::getUserId, userId)
                .eq(UserRetentionStatsDO::getUsageLogId, usageLogId)) > 0;
    }

    /**
     * 删除指定日期之前的记录
     */
    default int deleteByDateBefore(LocalDate beforeDate) {
        return delete(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .lt(UserRetentionStatsDO::getCodeUsageDate, beforeDate));
    }

    /**
     * 统计待计算记录数量
     */
    default long countPendingCalculation() {
        return selectCount(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .eq(UserRetentionStatsDO::getRetentionStatus, 0)); // 0-计算中
    }

    /**
     * 统计指定日期的记录数量
     */
    default long countByDate(LocalDate targetDate) {
        return selectCount(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .eq(UserRetentionStatsDO::getCodeUsageDate, targetDate));
    }

    /**
     * 统计指定日期已完成计算的记录数量
     */
    default long countCompletedByDate(LocalDate targetDate) {
        return selectCount(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .eq(UserRetentionStatsDO::getCodeUsageDate, targetDate)
                .eq(UserRetentionStatsDO::getRetentionStatus, 1)); // 1-已计算
    }

    /**
     * 统计总用户数
     */
    default long countTotalUsers(LocalDate startDate, LocalDate endDate) {
        return selectCount(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .betweenIfPresent(UserRetentionStatsDO::getCodeUsageDate, startDate, endDate)
                .groupBy(UserRetentionStatsDO::getUserId));
    }

    /**
     * 统计留存用户数
     */
    default long countRetentionUsers(LocalDate startDate, LocalDate endDate, int retentionDays) {
        LambdaQueryWrapperX<UserRetentionStatsDO> wrapper = new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .betweenIfPresent(UserRetentionStatsDO::getCodeUsageDate, startDate, endDate);
        
        if (retentionDays == 3) {
            wrapper.eq(UserRetentionStatsDO::getRetention3Days, true);
        } else if (retentionDays == 7) {
            wrapper.eq(UserRetentionStatsDO::getRetention7Days, true);
        } else if (retentionDays == 15) {
            wrapper.eq(UserRetentionStatsDO::getRetention15Days, true);
        }
        
        return selectCount(wrapper);
    }

    /**
     * 获取平均登录天数
     */
    @Select("SELECT AVG(total_login_days) FROM system_user_retention_stats " +
            "WHERE (#{startDate} IS NULL OR code_usage_date >= #{startDate}) " +
            "AND (#{endDate} IS NULL OR code_usage_date <= #{endDate})")
    Double getAverageLoginDays(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

    /**
     * 统计活跃用户数
     */
    default long countActiveUsers(LocalDate startDate, LocalDate endDate) {
        return selectCount(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .betweenIfPresent(UserRetentionStatsDO::getCodeUsageDate, startDate, endDate)
                .gt(UserRetentionStatsDO::getTotalLoginDays, 0));
    }

    /**
     * 根据用户ID查询留存率统计
     */
    default List<UserRetentionStatsDO> selectByUserId(Long userId, LocalDate startDate, LocalDate endDate) {
        return selectList(new LambdaQueryWrapperX<UserRetentionStatsDO>()
                .eq(UserRetentionStatsDO::getUserId, userId)
                .betweenIfPresent(UserRetentionStatsDO::getCodeUsageDate, startDate, endDate)
                .orderByDesc(UserRetentionStatsDO::getCodeUsageDate));
    }
}